![]() |
Java Database Programming with JDBC
by Pratik Patel Coriolis, The Coriolis Group ISBN: 1576100561 Pub Date: 10/01/96 |
Previous | Table of Contents | Next |
Many of the standard SQL-92 data types, such as Date, do not have a native Java equivalent. To overcome this deficiency, you must map SQL data types into Java. This process involves using JDBC classes to access SQL data types. In this chapter, well take a look at the classes in the JDBC that are used to access SQL data types. In addition, well briefly discuss the Object Relation Model (ORM), an interesting area in database development that attempts to map relational models into objects.
You need to know how to properly retrieve equivalent Java data typeslike int, long, and Stringfrom their SQL counterparts and store them in your database. This can be especially important if you are working with numeric data (which requires careful handling of decimal precision) and SQL timestamps (which have a well-defined format). The mechanism for handling raw binary data is touched on in this chapter, but it is covered in more detail in Chapter 8.
Mapping Java data types into SQL is really quite simple. Table 6.1 shows how Java data types map into equivalent SQL data types. Note that the types beginning with java.sql. are not elemental data types, but are classes that have methods for translating the data into usable formats.
Java Type | SQL Type |
---|---|
string | VARCHAR or LONGVARCHAR |
java.sql.Numeric | NUMERIC |
boolean | BIT |
byte | TINYINT |
short | SMALLINT |
int | INTEGER |
long | BIGINT |
float | REAL |
double | DOUBLE |
byte[] | VARBINARY or LONGVARBINARY |
java.sql.Date | DATE |
java.sql.Time | TIME |
java.sql.Timestamp | TIMESTAMP |
The byte[] data type is a byte array of variable size. This data structure is used to store binary data; binary data is manifest in SQL as VARBINARY and LONGVARBINARY. These types are used to store images, raw document files, and so on. To store or retrieve this data from the database, you would use the stream methods available in the JDBC: setBinaryStream and getBinaryStream. In Chapter 8, well use these methods to build a multimedia Java/JDBC application.
Table 6.2 shows the mapping of SQL data types into Java. You will find that both tables will come in handy when youre attempting to decide which types need special treatment. You can also use the tables as a quick reference to make sure that youre properly casting data that you want to store or retrieve.
Java Type | SQL Type |
---|---|
CHAR | String |
VARCHAR | String |
LONGVARCHAR | String |
NUMERIC | java.sql.Nueric |
DECIMAL | java.sql.Numeric |
BIT | boolean |
TINYINT | byte |
SMALLINT | short |
INTEGER | int |
BIGINT | long |
REAL | float |
FLOAT | double |
DOUBLE | souble |
BINARY | byte[] |
VARBINARY | byte[] |
LONGBINARY | byte[] |
DATE | java.sql.Date |
TIME | java.sql.Time |
TIMESTAMP | java.sql.Timestamp |
Now that youve seen how these data types translate from Java to SQL and vice versa, lets look at some of the methods that youll use to retrieve data from a database. These methods, shown in Table 6.3, are contained in the ResultSet class, which is the class that is passed back when you invoke a Statement.executeQuery function. Youll find a complete reference of the ResultSet class methods in Chapter 12.
The parameters int and String allow you to specify the column you want by column number or column name.
Method | Description |
---|---|
getAsciiStream(String), getAsciiStream(int) | Retrieves a column value as a stream of ASCII characters and then reads in chunks from the stream |
getBinaryStream(int), getBinaryStream(String) | Retrieves a column value as a stream of uninterpreted bytes and then reads in chunks from the stream |
getBoolean(int), getBoolean(String) | Returns the value of a column in the current row as a Java boolean |
getDate(int), getDate(String) | Returns the value of a column in the current row as a java.sql.Date object |
getObject(int), getObject(String) | Returns the value of a column as a Java object |
One of the most useful classes you can use to retrieve data from a ResultSet is the ResultSetMetaData class. This class contains methods that allow you to obtain vital information about the querys result. After a query has been executed, you can call the ResultSet.getMetaData method to fetch a ResultSetMetaData object for the resulting data. Table 6.4 shows some of the methods that you will most likely use. Again, more ResultSetMetaData methods are listed in Chapter 12.
Method | Description |
---|---|
getColumnCount() | Indicates the number of columns in the ResultSet |
getColumnLabel(int) | Returns the database-assigned Label for the column at position int in the ResultSet |
getColumnName(int) | Returns the columns name (for query reference) |
getColumnType(int) | Returns the specified columns SQL type |
isNullable(int) | Tells you if the specified column can contain NULLs |
isSearchable(int) | Indicates whether the specified column is searchable via a WHERE clause |
Previous | Table of Contents | Next |